import xlrd
import xlwt


# 打印所有异常情况，有三种模式，检查日期格式，检查仓储数目是否出错，




# 生成错误数组
def error_list(a):
    data = xlrd.open_workbook(a, 'rb')
    names = data.sheet_names()
    # 装载目前仓储数目为负数的记录
    num_error1 = []
    # 装载目前仓储数目不合法的记录
    num_error2 = []
    # 六个数中是否存在不能转为数字的项
    num_error3 = []
    # 装载时间格式的错误
    time_error = []
    # 目录部分不读取
    for i in names:
        table = data.sheet_by_name(i)
        # 默认开始行数
        line = 3
        # 结束行数
        nrows = table.nrows
        # print('---------')
        # print('小类名称', i, '行数', table.nrows)
        while line <= nrows - 1:
            # print('目前行数', line + 1)

            # 如果没写日期就跳过
            hxy = table.cell_value(line, 0)
            if hxy == '':
                line = line + 1
                continue

            try:
                a = table.cell_value(line, 2)
                b = table.cell_value(line, 3)
                c = table.cell_value(line, 4)
                d = table.cell_value(line, 5)
                e = table.cell_value(line, 6)
                f = table.cell_value(line, 7)
            except:
                print('---------')
                print('小类名称', i, '目前行数', line + 1)
                line = line + 1
                continue


            # 借方或者贷方哪边的单价总价数量三个位置都有数字就代表没问题，否则跳过
            if a != '' and b != '' and c != '':
                try:
                    temp1 = float(a)
                    temp1 = float(b)
                    temp1 = float(c)
                except:
                    num_error3.append()
            elif d != '' and e != '' and f != '':
                temp1 = float(d)
                temp1 = float(e)
                temp1 = float(f)
            else:
                line = line + 1
                continue

            # 检查两项异常，分别是日期格式不对和某处仓储数目为0

            if (a != '' and b != '' and c != '') or (d != '' and e != '' and f != ''):
                pass
            else:
                line = line + 1
                continue

                # 日期
            try:
                date = xlrd.xldate.xldate_as_datetime(table.cell_value(line, 0), 0).__format__(r'%Y-%m-%d')
            except:
                time_error.append([f'{table.cell(line, 0)}', f'{i}', f'{line + 1}', ' '])
                date = '出现错误'

            # 检查总数是否有负数的情况
            try:
                if float(table.cell_value(line, 8)) < 0:
                    num_error1.append([f'{date}', f'{i}', f'{line + 1}', f'{float(table.cell_value(line, 8))}'])
            except:
                num_error2.append([f'{date}', f'{i}', f'{line + 1}', '空置或格式错误'])

            line = line + 1

    print('错误记录生成完成')
    return num_error1, num_error2, num_error3, time_error


# 错误记录写入xls
def write_num_error_log(num_error1, num_error2, num_error3, time_error, a):
    title = ['日期', '名称', '行数', '目前数量']

    xl = xlwt.Workbook(encoding='utf-8')
    sheet1 = xl.add_sheet('sheet1', cell_overwrite_ok=False)

    # 设置时间样式
    # style1 = xlwt.XFStyle()
    # style1.num_format_str = 'yyyy-mm-dd'
    sheet1.col(0).width = 5000
    sheet1.col(1).width = 8000
    sheet1.col(2).width = 5000
    sheet1.col(3).width = 5000
    # 写入表头
    num = 0
    for i in title:
        sheet1.write(0, num, i)
        num = num + 1

    # 写入数目错误
    hang = 0  # 行

    # hang = hang + 1
    # sheet1.write(hang, 0, '以下部分为数目为负数')
    #
    # for k in num_error1:
    #     lie = 0
    #     hang = hang + 1
    #     for u in k:
    #         sheet1.write(hang, lie, u.strip())
    #         lie = lie + 1

    hang = hang + 1
    sheet1.write(hang, 0, '以下部分为数目不合法')

    for k in num_error2:
        lie = 0
        hang = hang + 1
        for u in k:
            sheet1.write(hang, lie, u.strip())
            lie = lie + 1

    hang = hang + 1
    sheet1.write(hang, 0, '以下部分为时间错误')

    # 写入时间错误
    for k in time_error:
        lie = 0
        hang = hang + 1
        for u in k:
            sheet1.write(hang, lie, u.strip())
            lie = lie + 1


    xl.save(a)
    print('存储为xls成功')


name = '轴承'

# 文件地址和写入地址
path1 = r'C:\Users\谢佩恒\Desktop\仓库项目\仓库备件记录台账\用导入的记录\\' + name + '.xls'
path2 = r'C:\Users\谢佩恒\Desktop\仓库项目\仓库备件记录台账\用导入的记录\故障处理\\' + name + '检查.xls'


# table1 = ['轴承', '油管', '铜阀', '螺丝-平键-天车配件', '快叉-刀头', '角带-链条', '机械加工', '焊接', '电器', '电工附件', '磁棒-锯片']
table1 = ['角带-链条']
temp1 = r'C:\Users\谢佩恒\Desktop\仓库项目\仓库备件记录台账\用导入的记录\\'
temp2 = r'.xls'
temp3 = r'C:\Users\谢佩恒\Desktop\仓库项目\仓库备件记录台账\用导入的记录\故障处理\\'

path1 =



num_error1, num_error2, num_error3, time_error = error_list(path1)
write_num_error_log(num_error1, num_error2, time_error, path2)